<?php
declare (strict_types = 1);

namespace app\home\controller;
// 引入必要类
use app\webadmin\model\Duibidu;
use app\webadmin\model\Junyunxing;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
use think\facade\Log;
use MathPHP\Probability\Distribution;



use MathPHP\Algebra;
class Index
{

    public function t10(){
        $spreadsheet = new Spreadsheet();
        // 获取活动 sheet
        $sheet = $spreadsheet->getActiveSheet(0);

        // 设置单元格A1的文本为普通文本
        $sheet->setCellValue('A1', '5');
        $sheet->setCellValue('B1', '15');
        $sheet->setCellValue('C1', '20');
        // 设置单元格A2的文本为下标值
        /*$sheet->getStyle('A2')->setFont(new Font([
            'size' => 12,
            'bold' => true,
            'italic' => false,
            'underline' => false,
            'strikethrough' => false,
            'superscript' => true, // 设置为下标
        ]));*/
        $sheet->setCellValue('A2', '10');

        // 合并单元格A1和A2，使它们显示为普通文本和下标值的组合
        //$sheet->mergeCells('A1:A2');

        // 在单元格A3中插入数学公式下标值 =FINV(0.05,C28,C29)
        //$sheet->setCellValue('A3', '=SUM(A1:A2)'); // A3单元格显示A2单元格下标的平方
        $sheet->setCellValue('A3', '=SUM(A1:C1)'); // A3单元格显示A2单元格下标的平方

        ob_end_clean();
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . '门店面单' . '.xls"');
        header("Content-Disposition:attachment;filename=门店面单.xls"); //attachment新窗口打印inline本窗口打印
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    public function index()
    {
        //return view('index');
        echo 'index';

        /*$alpha = 0.05; // 显著性水平
        $d1 = 5; // 自由度1
        $d2 = 10; // 自由度2
        //$gcd = Algebra::gcd(8, 12);
        //echo $gcd;
        $gcd = Algebra::extendedGcd(12, 8);
        var_dump($gcd) ;
        $alpha = 0.05; // 显著性水平
        $d1 = 5; // 自由度1
        $d2 = 10; // 自由度2

        $result = D($d1, $d2, $alpha);
        echo "F 分布的逆函数为: " . $result;
        exit();
        //$result = Distribution::inverseF($d1, $d2, $alpha);
        //echo "F 分布的逆函数为: " . $result;
        //$presentValue=Functions::F(0.05,14,30);
        //$icdf = finv(0.05, 14, 30);
        //$presentValue = finv($rate = 0.05, 14, 30);
        //echo $icdf;
        // 使用示例
        $p = 0.05; // 概率值
        $d1 = 5; // 自由度1
        $d2 = 10; // 自由度2

        //$result = finv($p, 14, 30);
        //echo "F分布的逆函数为: " . var_export($result);
       // var_dump($result);
        //$probability = 0.5; // 概率值
        //$mean = 0; // 平均值，默认为0
        //$standard_deviation = 1; // 标准差，默认为1
*/
        //=SMALL(C3:J3,1)
        //Statistical::SMALL()
        exit();
        $spreadsheet = new Spreadsheet();
        // 获取活动 sheet
        $sheet = $spreadsheet->getActiveSheet(0);
        // 设置表格全部上下居中
        $defaultStyle = $spreadsheet->getDefaultStyle();
        $defaultStyle->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER)->setVertical(Alignment::VERTICAL_CENTER);
        $defaultStyle->getFont()->getColor()->setRGB('333333');

        // 设置列宽
        $sheet->getColumnDimension('A')->setWidth(7);
        $sheet->getColumnDimension('B')->setWidth(35);
        $sheet->getColumnDimension('C')->setWidth(11);
        $sheet->getColumnDimension('D')->setWidth(12);
        $sheet->getColumnDimension('E')->setWidth(12);
        $sheet->getColumnDimension('F')->setWidth(0);           // 预留列
        $sheet->getColumnDimension('G')->setWidth(14);

        $line = 1;
        // 大标题
        // 合并单元格
        $sheet->mergeCells('A'. $line .':G'. $line);            // 合并单元格
        $sheet->getRowDimension($line)->setRowHeight(40);       // 设置行高
        $ATitle = $sheet->getCell('A' . $line);                 // 获取单元格
        $ATitle->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);        // 内容水平居中
        $ATitle->getStyle('A' . $line)->getFont()->setSize(22)->setBold(true);                              // 字体大小，加粗
        $ATitle->setValue('Smallnews - 门店订单');

        $line ++;
        // 店长信息
        $sheet->mergeCells('A' . $line . ':G' . $line);
        $sheet->getStyle('A' . $line . ':G' . $line)->getBorders()->getBottom()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $AStore = $sheet->getCell('A' . $line);
        $AStore->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);                          // 内容水平居左
        $AStore->getStyle('A' . $line)->getFont()->setSize(16)->setBold(true);                                              // 字体大小，加粗
        $AStore->setValue('Smallnews/157****1560');

        $line ++;
        // 门店地址
        $sheet->mergeCells('A' . $line . ':G' . $line);
        $AAddress = $sheet->getCell('A' . $line);
        $AAddress->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
        $AAddress->getStyle('A' . $line)->getFont()->setSize(14);
        $AAddress->setValue('北京望京 SOHO');
        //$AAddress->setValue('A'.$line, '=TINV(0.95,0,1)');
        $line ++;
        // 运单统计
        $sheet->mergeCells('A' . $line . ':B' . $line);         // AB 合并
        $sheet->getRowDimension($line)->setRowHeight(40);       // 设置行高
        $ATotalOrder = $sheet->getCell('A' . $line);
        $ATotalOrder->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT)->setVertical(Alignment::VERTICAL_BOTTOM);        // 内容水平居左，垂直居下
        $ATotalOrder->getStyle('A' . $line)->getFont()->setSize(12);
        $ATotalOrder->setValue('订单数量：5');

        $sheet->mergeCells('C' . $line . ':D' . $line);         // CD 合并
        $CTotalGoods = $sheet->getCell('C' . $line);
        $CTotalGoods->getStyle('C' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT)->setVertical(Alignment::VERTICAL_BOTTOM);        // 内容水平居左，垂直居下
        $CTotalGoods->getStyle('C' . $line)->getFont()->setSize(12);
        $CTotalGoods->setValue('商品总量：20');

        $sheet->mergeCells('E' . $line . ':G' . $line);         // EFG 合并
        $ESend = $sheet->getCell('E' . $line);
        $ESend->getStyle('E' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT)->setVertical(Alignment::VERTICAL_BOTTOM);             // 内容水平居左，垂直居下
        $ESend->getStyle('E' . $line)->getFont()->setSize(12);
        $ESend->setValue('发货时间：' . date('Y-m-d'));

        $line ++;
        // 增加一个空行，充当上下内容的 margin
        $sheet->mergeCells('A' . $line . ':G' . $line);
        $sheet->getRowDimension($line)->setRowHeight(6);

        $line ++;

        // 模拟订单数据
        $orders = [
            ['items' => [
                ['goods_title' => '这是个名字很长的商品，真的很长， 不信你看，肯定超过了表格宽度'],
                ['goods_title' => '这是个名字比较短的商品'],
            ]],
            ['items' => [
                ['goods_title' => '转向 卫衣秋季潮牌新款宽松时尚套头紫橘色橙色短款连帽卫衣女'],
                ['goods_title' => '芙清医美面膜医用男女淡化痘印抗菌敷料水光针术后修复皮炎祛痘'],
                ['goods_title' => '经典麻辣锅底'],
            ]]
        ];

        // 订单数据
        foreach ($orders as $order) {
            // 购买信息
            $sheet->getRowDimension($line)->setRowHeight(30);
            $sheet->getStyle('A' . $line . ':G' . $line)->getFont()->setSize(14);
            $sheet->getStyle('A' . $line . ':G' . $line)->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
            $sheet->mergeCells('A' . $line . ':B' . $line);
            $AUser = $sheet->getCell('A' . $line);
            $AUser->getStyle('A' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);
            $AUser->getStyle('A' . $line)->getFont()->setSize(15)->setBold(true);

            // 模拟用户数据
            $user = [ 'nickname' => 'Smallnews', 'mobile' => '15788881560' ];
            $nickname = mb_strlen($user['nickname']) > 7 ? mb_substr($user['nickname'], 0, 6) . '**' : $user['nickname'];
            $AUser->setValue($nickname . ($user['mobile'] ?  ' /  ' .substr($user['mobile'], 0, 3) . '****' . substr($user['mobile'], 7) : ''));

            $sheet->mergeCells('C' . $line . ':G' . $line);
            $CTotal = $sheet->getCell('C' . $line);
            $CTotal->getStyle('C' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_RIGHT);
            $CTotal->getStyle('C' . $line)->getFont()->setSize(14);
            $CTotal->setValue('共 2 种商品，共 3 件，实付 200 元');

            $line++;
            // 增加一个空行，充当上下内容的 margin
            $sheet->mergeCells('A' . $line . ':G' . $line);
            $sheet->getRowDimension($line)->setRowHeight(6);

            $line ++;
            // 订单商品信息
            $sheet->getStyle('A' . $line . ':G' . ($line + count($order['items'])))->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);     // 根据商品数量， 设置区域的边框

            $sheet->setCellValue('A' . $line, '序号');
            $sheet->setCellValue('B' . $line, '商品名称');
            $sheet->setCellValue('C' . $line, '单价');
            $sheet->setCellValue('D' . $line, '优惠');
            $sheet->setCellValue('E' . $line, '数量');
            $sheet->setCellValue('F' . $line, '');
            $sheet->setCellValue('G' . $line, '是否提货');

            foreach ($order['items'] as $key => $item) {
                $line ++;
                $sheet->setCellValue('A' . $line, ($key + 1));
                $sheet->getStyle('B' . $line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);               // 商品名称 水平居左
                $goods_title = mb_strlen($item['goods_title']) > 16 ? mb_substr($item['goods_title'], 0, 14) . '**' : $item['goods_title'];
                $sheet->setCellValue('B' . $line, $goods_title);
                $sheet->setCellValue('C' . $line, '22.22');
                $sheet->setCellValue('D' . $line, '11.11');
                $sheet->setCellValue('E' . $line, 3);
                $sheet->setCellValue('F' . $line, '');
                $sheet->setCellValue('G' . $line, '');
            }
            $line++;
            $sheet->mergeCells('A' . $line . ':G' . $line);
            $sheet->getRowDimension($line)->setRowHeight(6);
            $line++;
        }

        // 合并单元格
        $sheet->mergeCells('A19' .':A30');            // 合并单元格
        $sheet->mergeCells('B19' .':B30');            // 合并单元格


        //设置居中
        $sheet->getStyle('A19')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->getStyle('A19')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('A19')->getAlignment()->setWrapText(true);

        $sheet->getStyle('B19')->getAlignment()->setVertical(Alignment::VERTICAL_CENTER);
        $sheet->getStyle('B19')->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
        $sheet->getStyle('B19')->getAlignment()->setWrapText(true);

        // 设置合并后的单元格的值
        $sheet->setCellValue('A19', '22');
        $sheet->setCellValue('B19', 'tt');

        ob_end_clean();
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . '门店面单' . '.xls"');
        header("Content-Disposition:attachment;filename=门店面单.xls"); //attachment新窗口打印inline本窗口打印
        $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }

    public function test4(){
        // 读取Excel文件
       // $spreadsheet = IOFactory::load('chongfu.xlsx');
        //$worksheet = $spreadsheet->getActiveSheet();

        // 计算B40:G40单元格的标准差
        $range = 'B40:G40';
        //$stdDev = Statistical::STDEV(array(34760,34460,35800,35040,34240,35960));

        //=(((I41^2)*($I$46-1)+($I$40^2)*($I$46-1))*$I$46*2/(($I$46*2-2)*($I$46^2)))^0.5
        $dt= pow((((pow(568,2))*(6-1)+(pow(704,2))*(6-1))*6*2/((6*2-2)*(pow(6,2)))),0.5);
        //echo "标准差： " . $stdDev;
        echo $dt;
    }

    public function test(){

        //$multiArr = array(array('apple', 'banana', 'pear'), array('cat', 'dog', 'lion'));

       // $singleArr = call_user_func_array('array_merge', $multiArr);

        //print_r($singleArr);
        //$junyunxing=Junyunxing::order('id asc')->field('yp1,yp2')->select()->toArray();
        $junyunxing=Junyunxing::order('id asc')->column('yp1,yp2,yp3');
        //echo json_encode(flatten($junyunxing));
        $range=flatten($junyunxing);
        $average = Statistical::AVERAGE($range);

       //Log::info('junyunxing='.json_encode($junyunxing));
        Log::info('average='.$average);
        echo json_encode($average);
        //Log::info('arr='.json_encode($arr));
        //$junyunxing2=call_user_func_array('array_merge',$junyunxing);
        //echo json_encode(array_column($junyunxing,'yp1,yp2,yp3'));
        //$probability = 0.5; // 概率值，范围为0到1之间
       // $zScore= Statistical::TINV(0.5,1);
        //$zScore = Statistical::TINV($probability); // 计算z分数
        //echo "Z分数： " . $zScore;

        //$sum_pf=sum_pingfang([33840,33640,33920]);
        //echo $sum_pf;

        // 创建一个新的Spreadsheet对象
        /*$spreadsheet = new \PhpOffice\PhpSpreadsheet\Spreadsheet();
        // 获取当前活动的工作表
        $worksheet = $spreadsheet->getActiveSheet();
        // 在A1单元格中写入数据
        $worksheet->setCellValue('A1', '=TINV(0.95,0,1)');
        // 保存Excel文件到本地
        $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
        $writer->save('example.xlsx');*/
    }

    public function test2(){
        set_time_limit(0);
        $date=date('Y-m-d');
        $spreadsheet = new Spreadsheet();
        //合并单元格
        $spreadsheet->getActiveSheet()->mergeCells('A1:E1');
        //设置自动高度
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setAutoSize(true);
        //设置宽度
        $spreadsheet->getActiveSheet()->getColumnDimension('B')->setWidth(100);
        //设置字体为粗体
        $spreadsheet->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //设置表头
        $spreadsheet->getActiveSheet()->getCell('A1')->setValue('销售汇总单（' . $date . '）');


        //设置自动换行
        $spreadsheet->getActiveSheet()->getStyle('A3:E100')->getAlignment()->setWrapText(true);
        //设置水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $worksheet = $spreadsheet->getActiveSheet();

        $worksheet->getStyle('A1:E100')->applyFromArray($styleArray);

        $title = ['序号', '商品名称', '规格'];
        foreach ($title as $index => $item) {
            $worksheet->setCellValueByColumnAndRow($index + 1, 2, $item);
        }

        $data = [
            ['no' => 1, 'product_name' => '苹果', 'package' => '1斤'],
            ['no' => 2, 'product_name' => '香蕉', 'package' => '2斤'],
            ['no' => 3, 'product_name' => '香蕉', 'package' => '3斤'],
        ];

        $row = 2; // 从第二行开始
        foreach ($data as $item) {
            $column = 1;
            foreach ($item as $value) {
                // 单元格内容写入
                $worksheet->setCellValueByColumnAndRow($column, $row, $value);
                $column++;
            }
            $row++;
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="销售汇总单（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
    }

    public function export(){
        $duibidu=new Duibidu();
        $list = $duibidu->field('id,codes,name,price')
            ->select()
            ->toArray();
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('发货单');//设置excel的标题

        $objActSheet->getRowDimension(1)->setRowHeight(30);
        $objActSheet->getStyle('A1:D1')->getFont()->setSize(14);
        $objActSheet->getStyle('A1:D1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(10);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);
        $objActSheet->getColumnDimension('D')->setWidth(14);

        $objActSheet->getRowDimension(1)->setRowHeight(30);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            'alignment' => [
                'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER,
                'vertical' => \PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER,
            ],
        ];

        $objActSheet->getStyle('A1:D100')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', 'id');
        $objActSheet->setCellValue('B1', '股票代码');
        $objActSheet->setCellValue('C1', '名称');
        $objActSheet->setCellValue('D1', '价格');
        $baseRow = 2; //数据从N-1行开始往下输出
       foreach ($list as $r => $d) {

            $i = $baseRow + $r;
            $objActSheet->getRowDimension($i)->setRowHeight(30);
            $objActSheet->getStyle('A' . $i . ':D' . $i)->getFont()->setSize(12);
            $objExcel->getActiveSheet()->setCellValue('A' . $i, $d['id']);
            $objExcel->getActiveSheet()->setCellValue('B' . $i, $d['codes']);
            $objExcel->getActiveSheet()->setCellValue('C' . $i, $d['name']);
            $objExcel->getActiveSheet()->setCellValue('D' . $i, $d['price']);
        }

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="股票信息（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');

    }

}
